package user.com;

import java.sql.Connection;


import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Date;
	public class myjdbc {
		
		private Connection conn=null;
		private Statement sm=null;
		private ResultSet rs=null;
		
		public void getConnAndSm() throws ClassNotFoundException, SQLException{
			String url="jdbc:mysql://127.0.0.1:3306/mytest?"
					+ "useUnicode=true&amp;characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver");
			String user="root";
			String pwd="x5";
			conn=DriverManager.getConnection(url, user, pwd);
			sm=conn.createStatement();
		}
		
		public void insert(String sql) throws SQLException, ClassNotFoundException{
			getConnAndSm();
			sm.executeUpdate(sql);
			sm.close();
			conn.close();
		}
		
		public void delete(String sql) throws SQLException, ClassNotFoundException{
			getConnAndSm();
			sm.executeUpdate(sql);
			sm.close();
			conn.close();
		}
		
		public void update(String sql) throws SQLException, ClassNotFoundException{
			getConnAndSm();
			sm.executeUpdate(sql);
			sm.close();
			conn.close();
		}
		
		public List<Map<String,Object>> select(String sql) throws SQLException, ClassNotFoundException{
			getConnAndSm();
			rs=sm.executeQuery(sql);
			List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
			while(rs.next()){
				
				String a=rs.getString("zhanghao");
				String b=rs.getString("mima");
				String c=rs.getString("mibao");
				Map<String,Object> map1=new HashMap<String,Object>();
				map1.put("zhanghao", a);
				map1.put("mima", b);
				map1.put("mibao", c);
				list.add(map1);
			}
			rs.close();
			sm.close();
			conn.close();
			return list;
		}
		public String select1(String name) throws SQLException, ClassNotFoundException{
			getConnAndSm();
			String sql="select mibao from user where zhanghao="+name;
			rs=sm.executeQuery(sql);
			String b = null;
			while(rs.next()){
				b=rs.getString("mibao");
			}
			rs.close();
			sm.close();
			conn.close();
			return b;
		}
		public List<Map<String,Object>> fenye(String table2) throws SQLException, ClassNotFoundException{
			getConnAndSm();
			String sql="select xuhao,name,zuozhe,price,riqi from "+table2;
			rs=sm.executeQuery(sql);
			List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
			while(rs.next()){
				int i=rs.getInt("xuhao");
				String a=rs.getString("name");
				String b=rs.getString("zuozhe");
				double c=rs.getDouble("price");
				Date e=rs.getDate("riqi");
				Map<String,Object> map1=new HashMap<String,Object>();
				map1.put("name", a);
				map1.put("zuozhe", b);
				map1.put("price", c);
				map1.put("xuhao", i);
				map1.put("riqi", e);
				list.add(map1);
			}
			rs.close();
			sm.close();
			conn.close();
			return list;
		}
		public int select2(String table1) throws SQLException, ClassNotFoundException{
			getConnAndSm();
			String sql="select count(*) from "+table1;
			rs=sm.executeQuery(sql);
			int b =0;
			while(rs.next()){
				b=rs.getInt("count(*)");
			}
			rs.close();
			sm.close();
			conn.close();
			return b;
		}
	}
